package de.playmee.handler;

import java.util.ArrayList;
import java.util.List;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.graphics.Bitmap;
import android.graphics.BitmapFactory;
import android.media.MediaMetadataRetriever;
import android.net.Uri;
import android.provider.MediaStore;
import android.util.Log;
import de.playmee.Track;
import de.playmee.Weather;
import de.playmee.Track.Status;
import de.playmee.Weather.Condition;
import de.playmee.XMLParser;
import de.playmee.enums.Mood;
import de.playmee.enums.Result;
import de.playmee.enums.TimeOfDay;
import de.playmee.enums.Season;
import de.playmee.tasks.PrepareHandlerTask.AnalyzeMusicListener;

public class MusicHandler extends DatabaseHandler {

	private final String TAG = "MusicHandler";
	
	// query for all audio media on external storage (e.g. SD card)
	public final static Uri MEDIA_URI = MediaStore.Audio.Media.EXTERNAL_CONTENT_URI;
	
	// standard attributes supported by android
	public final static String KEY_DATA = MediaStore.Audio.Media.DATA;
	public final static String KEY_ID_DEVICE = MediaStore.Audio.Media._ID;
	public final static String KEY_ARTIST = MediaStore.Audio.Media.ARTIST;
	public final static String KEY_TITLE = MediaStore.Audio.Media.TITLE;
	public final static String KEY_ALBUM = MediaStore.Audio.Media.ALBUM;
	public final static String KEY_RELEASE = MediaStore.Audio.Media.YEAR;
	public final static String KEY_DURATION = MediaStore.Audio.Media.DURATION;
	
	// additional attributes
	public final static String KEY_ID_DB = "id_db";
	public final static String KEY_STATUS = "status";
	public final static String KEY_ART = "art";
	public final static String KEY_BPM = "bpm";
	public final static String KEY_MOODS = "moods";
	public final static String KEY_TIMESOFDAY = "timesOfDay";
	public final static String KEY_SEASONS = "seasons";
	public final static String KEY_WEATHERS = "weather";
	
	// table names
	public final static String DATABASE_TABLE_MUSIC = "music";
	public final static String DATABASE_TABLE_CHECK = "toCheck";
	public final static String DATABASE_TABLE_UPLOAD = "toUpload";
	
	// sql command to create table for music data
	public final static String DATABASE_CREATE_MUSIC = "create table " + DATABASE_TABLE_MUSIC
			+ " (" + KEY_DATA + " text primary key, "
			+ KEY_ID_DEVICE + " bigint not null, "
			+ KEY_ID_DB + " bigint not null, "
			+ KEY_STATUS + " text not null, "
			+ KEY_ART + " text not null, "
			+ KEY_BPM + " integer not null, "
			+ KEY_MOODS + " text not null, "
			+ KEY_TIMESOFDAY + " text not null, "
			+ KEY_SEASONS + " text not null, "
			+ KEY_WEATHERS + " text not null);";
	
	// sql command to create table for tracks that have to be checked
	public final static String DATABASE_CREATE_CHECK = "create table " + DATABASE_TABLE_CHECK
			+ " (" + KEY_DATA + " text primary key);";
	
	// sql command to create table for tracks that have to be uploaded
	public final static String DATABASE_CREATE_UPLOAD = "create table " + DATABASE_TABLE_UPLOAD
			+ " (" + KEY_DATA + " text primary key);";
	
	// order by artist and title (a..z, ignore case)
	public final static String ORDER_BY_ARTIST_TITLE_ABC = KEY_ARTIST + " collate nocase asc, "
			+ KEY_TITLE + " collate nocase asc";
	
	// order by artist and title (z..a, ignore case)
	public final static String ORDER_BY_ARTIST_TITLE_CBA = KEY_ARTIST + " collate nocase desc, "
			+ KEY_TITLE + " collate nocase desc";
	
	// content resolver
	private ContentResolver contentResolver;
	
	// the resources to get drawables
	Resources resources;
	
	// account handler
	AccountHandler aHandler;
	
	public MusicHandler(DatabaseHelper dbHelper, ContentResolver contentResolver,
			Resources resources, AccountHandler aHandler) {
		super(dbHelper);
		// now available: dbHelper, db, DELIMITER
		this.contentResolver = contentResolver;
		this.resources = resources;
		this.aHandler = aHandler;
	}
	
	/**
	 * inserts a track to DATABASE_TABLE_MUSIC
	 * 
	 * @param track
	 * @return {@link SQLiteDatabase#insert(String, String, ContentValues)}
	 */
	public long insertTrack(Track track) {
		Log.i(TAG, "Inserting track " + track.getId() + " to " + DATABASE_TABLE_MUSIC + "...");
		synchronized(db) {
			return db.insert(DATABASE_TABLE_MUSIC, null, getContentValues(track));
		}
	}
	
	/**
	 * inserts a track to DATABASE_TABLE_CHECK
	 * 
	 * @param track
	 * @return
	 */
	public long insertTrackToChecklist(String data, boolean checkBefore) {
		int count = 0;
		if (checkBefore)
			count = getCount(DATABASE_TABLE_CHECK, KEY_DATA + "='" + data + "'");
		
		if (count == 0) {
			Log.i(TAG, "Inserting track '" + data + "' to " + DATABASE_TABLE_CHECK + "...");
			ContentValues contentValues = new ContentValues();
			contentValues.put(KEY_DATA, data);
			synchronized(db) {
				return db.insert(DATABASE_TABLE_CHECK, null, contentValues);
			}
		}
		return -1;
	}
	
	/**
	 * inserts a track to DATABASE_TABLE_UPLOAD
	 * 
	 * @param track
	 * @return
	 */
	public long insertTrackToUploadlist(String data, boolean checkBefore) {
		int count = 0;
		if (checkBefore)
			count = getCount(DATABASE_TABLE_UPLOAD, KEY_DATA + "='" + data + "'");
		
		if (count == 0) {
			Log.i(TAG, "Inserting track '" + data + "' to " + DATABASE_TABLE_UPLOAD + "...");
			ContentValues contentValues = new ContentValues();
			contentValues.put(KEY_DATA, data);
			synchronized(db) {
				return db.insert(DATABASE_TABLE_UPLOAD, null, contentValues);
			}
		}
		return -1;
	}
	
	/**
	 * deletes a particular track from DATABASE_TABLE_MUSIC
	 * 
	 * @param _id
	 * @return {@link SQLiteDatabase#delete(String, String, String[])}
	 */
	public boolean deleteTrack(long _id) {
		Log.i(TAG, "Deleting track " + _id + " from " + DATABASE_CREATE_MUSIC + "...");
		synchronized(db) {
			return db.delete(DATABASE_TABLE_MUSIC, KEY_ID_DEVICE + "=" + _id, null) > 0;
		}
	}
	
	/**
	 * deletes a particular track from DATABASE_TABLE_MUSIC
	 * 
	 * @param _data
	 * @return {@link SQLiteDatabase#delete(String, String, String[])}
	 */
	public boolean deleteTrackWithHelpOfPath(String _data) {
		Log.i(TAG, "Deleting track " + _data + " from " + DATABASE_CREATE_MUSIC + "...");
		synchronized(db) {
			return db.delete(DATABASE_TABLE_MUSIC, KEY_DATA + "='" + _data + "'", null) > 0;
		}
	}
	
	/**
	 * deletes a particular track from DATABASE_TABLE_CHECK
	 * 
	 * @param _id
	 * @return {@link SQLiteDatabase#delete(String, String, String[])}
	 */
	public boolean deleteTrackFromChecklist(String data) {
		Log.i(TAG, "Deleting track '" + data + "' from " + DATABASE_TABLE_CHECK + "...");
		synchronized(db) {
			return db.delete(DATABASE_TABLE_CHECK, KEY_DATA + "='" + data + "'", null) > 0;
		}
	}
	
	/**
	 * deletes a particular track from DATABASE_TABLE_UPLOAD
	 * 
	 * @param _id
	 * @return {@link SQLiteDatabase#delete(String, String, String[])}
	 */
	public boolean deleteTrackFromUploadlist(String data) {
		Log.i(TAG, "Deleting track '" + data + "' from " + DATABASE_TABLE_UPLOAD + "...");
		synchronized(db) {
			return db.delete(DATABASE_TABLE_UPLOAD, KEY_DATA + "='" + data + "'", null) > 0;
		}
	}
	
	/**
	 * truncate DATABASE_TABLE_CHECK
	 */
	public void truncateChecklist() {
		Log.i(TAG, "Truncating " + DATABASE_TABLE_CHECK + "...");
		synchronized(db) {
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_CHECK);
			db.execSQL(DATABASE_CREATE_CHECK);
		}
	}
	
	/**
	 * truncate DATABASE_TABLE_UPLOADK
	 */
	public void truncateUploadlist() {
		Log.i(TAG, "Truncating " + DATABASE_TABLE_UPLOAD + "...");
		synchronized(db) {
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_UPLOAD);
			db.execSQL(DATABASE_CREATE_UPLOAD);
		}
	}
	
	/**
	 * truncate DATABASE_TABLE_MUSIC
	 */
	public void truncate() {
		Log.i(TAG, "Truncating " + DATABASE_TABLE_MUSIC + "...");
		synchronized(db) {
			db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE_MUSIC);
			db.execSQL(DATABASE_CREATE_MUSIC);
		}
	}
	
	/**
	 * retrieves all tracks with all available attributes
	 * 
	 * @param selection
	 * @param sortOrder
	 * @param complete
	 * @return list
	 */
	public ArrayList<Track> loadTracks(String selection, String sortOrder, boolean complete) {
		Log.i(TAG, "Querying all tracks...");
		ArrayList<Track> tracks = new ArrayList<Track>();
		
		if (selection != null && !selection.isEmpty())
			selection += " and ";
		else
			selection = "";
			
		selection += MediaStore.Audio.Media.IS_MUSIC + " = 1";
		synchronized(contentResolver) {
			Cursor cur = contentResolver.query(MEDIA_URI, null, selection, null, sortOrder);
			if (cur != null && cur.moveToFirst()) {
				do {
					Track track = createTrack(cur);
					if (complete)
						completeTrack(track);
					tracks.add(track);
				} while (cur.moveToNext());
			}
			cur.close();
		}
		Log.i(TAG, "Done loading all tracks.");
		return tracks;
	}
	
	/**
	 * retrieves all tracks given by a document
	 * 
	 * @param doc
	 * @param complete
	 * @param loadAlbumArt
	 * @return List
	 */
	public List<Track> loadTracks(Document doc, boolean complete,
			boolean loadAlbumArt) {
		Log.i(TAG, "Creating all tracks from a document...");
		List<Track> tracks = new ArrayList<Track>();
		if (doc != null) {
			NodeList ratingNodes = doc.getElementsByTagName("rating");
			for (int i = 0; i < ratingNodes.getLength(); i++) {
				Element rating = (Element) ratingNodes.item(i);
				NodeList idNodes = rating.getElementsByTagName("id");
				if (idNodes.getLength() != 1)
					continue;
				long id = Long.parseLong(idNodes.item(0).getTextContent().trim());
				Track track = loadTrackWithHelpOfDatabaseId(id, complete);
				if (loadAlbumArt)
					loadAlbumArt(track);
				tracks.add(track);
			}
		}
		return tracks;
	}
	
	/**
	 * retrieves all track ids
	 * 
	 * @return list
	 */
	public ArrayList<Long> loadTrackIds() {
		Log.i(TAG, "Querying all track ids...");
		ArrayList<Long> trackIds = new ArrayList<Long>();
		
		synchronized(db) {
			Cursor cur = db.query(DATABASE_TABLE_MUSIC, new String[]{ KEY_ID_DB }, null, null, null, null, null);
			if (cur != null && cur.moveToFirst()) {
				int idIndex = cur.getColumnIndex(KEY_ID_DB);
				do {
					long id = cur.getLong(idIndex);
					trackIds.add(id);
				} while (cur.moveToNext());
			}
		}
		
		return trackIds;
	}
	
	/**
	 * retrieves a particular track with all available attributes
	 * 
	 * @param _id
	 * @param complete
	 * @return Track
	 */
	public Track loadTrack(long _id, boolean complete) {
		Log.i(TAG, "Queriying particular track with id " + _id + "...");
		Track track = null;
		String selection = KEY_ID_DEVICE + " = " + _id;
		synchronized(contentResolver) {
			Cursor cur = contentResolver.query(MEDIA_URI, null, selection, null, null);
			if (cur != null && cur.moveToFirst()) {
				track = createTrack(cur);
				if (complete)
					completeTrack(track);
			}
			cur.close();
		}
		Log.i(TAG, "Done loading track.");
		return track;
	}
	
	/**
	 * retrieves a particular track with all available attributes
	 * 
	 * @param _id
	 * @param complete
	 * @return Track
	 */
	public Track loadTrackWithHelpOfPath(String _data, boolean complete) {
		_data = deEscapePath(_data).replaceAll("'", "''");
		Log.i(TAG, "Queriying particular track with data '" + _data + "'...");
		Track track = null;
		String selection = KEY_DATA + " = '" + _data + "'";
		synchronized(contentResolver) {
			Cursor cur = contentResolver.query(MEDIA_URI, null, selection, null, null);
			if (cur != null && cur.moveToFirst()) {
				track = createTrack(cur);
				if (complete)
					completeTrack(track);
			}
			cur.close();
		}
		Log.i(TAG, "Done loading track.");
		return track;
	}
	
	/**
	 * retrieves a particular track with all available attributes
	 * 
	 * @param _id
	 * @param complete
	 * @return Track
	 */
	public Track loadTrackWithHelpOfDatabaseId(long _id, boolean complete) {
		Log.i(TAG, "Queriying particular track with database id '" + _id + "'...");
		String selection = KEY_ID_DB + "=" + _id;
		synchronized(db) {
			Cursor cur = db.query(DATABASE_TABLE_MUSIC, new String[]{ KEY_DATA },
					selection, null, null, null, null);
			if (cur != null && cur.moveToFirst()) {
				String data = cur.getString(cur.getColumnIndex(KEY_DATA)).trim();
				return loadTrackWithHelpOfPath(data, complete);
			}
		}
		return null;
	}
	
	/**
	 * retrieves a particular track from the device
	 * 
	 * @param _id
	 * @return
	 */
	public Track loadTrackFromDevice(long _id) {
		Track track = null;
		String selection = KEY_ID_DEVICE + " = " + _id;
		synchronized(contentResolver) {
			Cursor cur = contentResolver.query(MEDIA_URI, null, selection, null, null);
			if (cur != null && cur.moveToFirst()) {
				track = createTrack(cur);
			}
			cur.close();
		}
		return track;
	}
	
	/**
	 * updates a track
	 * 
	 * @param channel
	 * @return {@link SQLiteDatabase#update(String, ContentValues, String, String[])}
	 */
	public boolean updateTrack(Track track) {
		Log.i(TAG, "Updating track " + track.getId() + "...");
		synchronized(db) {
			return db.update(DATABASE_TABLE_MUSIC, getContentValues(track),
					KEY_DATA + "='" + track.getData() + "'", null) > 0;
		}
	}
	
	/**
	 * creates a track from given cursor
	 * 
	 * @param cur
	 * @return Track
	 */
	public Track createTrack(Cursor cur) {
		// get column indices
		int idDeviceIndex = cur.getColumnIndex(KEY_ID_DEVICE);
		int dataIndex = cur.getColumnIndex(KEY_DATA);
		int artistIndex = cur.getColumnIndex(KEY_ARTIST);
		int titleIndex = cur.getColumnIndex(KEY_TITLE);
		int albumIndex = cur.getColumnIndex(KEY_ALBUM);
		int releaseIndex = cur.getColumnIndex(KEY_RELEASE);
		int durationIndex = cur.getColumnIndex(KEY_DURATION);
		
		// get values of the row
		long idDevice = cur.getLong(idDeviceIndex);
		String data = cur.getString(dataIndex).trim();
		String artist = cur.getString(artistIndex).trim();
		String title = cur.getString(titleIndex).trim();
		String album = cur.getString(albumIndex).trim();
		int release = cur.getInt(releaseIndex);
		int duration = cur.getInt(durationIndex);
		
		// create track and define attributes
		Track track = new Track(idDevice, escapePath(data), resources);
		track.setArtist(artist);
		track.setTitle(title);
		track.setAlbum(album);
		track.setRelease(release);
		track.setDuration(duration);
		return track;
	}
	
	/**
	 * completes a track with data from DATABASE_TABLE_MUSIC
	 * 
	 * @param track
	 */
	private void completeTrack(Track track) {
		Log.i(TAG, "Completing track with id " + track.getId() + "...");
		
		synchronized(db) {
			Cursor cur = db.query(true, DATABASE_TABLE_MUSIC, null, KEY_DATA
					+ "='" + track.getData() + "'", null, null, null, null, null);
			if (cur != null && cur.moveToFirst()) {
				// get column indices
				int idDbIndex = cur.getColumnIndex(KEY_ID_DB);
				int artIndex = cur.getColumnIndex(KEY_ART);
				int bpmIndex = cur.getColumnIndex(KEY_BPM);
				int statusIndex = cur.getColumnIndex(KEY_STATUS);
				int moodsIndex = cur.getColumnIndex(KEY_MOODS);
				int seasonsIndex = cur.getColumnIndex(KEY_SEASONS);
				int timesOfDayIndex = cur.getColumnIndex(KEY_TIMESOFDAY);
				int weathersIndex = cur.getColumnIndex(KEY_WEATHERS);
			
				// set values
				track.setIdDatabase(cur.getLong(idDbIndex));
				track.setArtLink(cur.getString(artIndex).trim());
				track.setBpm(cur.getInt(bpmIndex));
				track.setStatus(Status.valueOf(cur.getString(statusIndex).trim()));
			
				String moods = cur.getString(moodsIndex);
				if (!moods.isEmpty()) {
					String[] m = moods.split(DELIMITER);
					for (String mood : m) {
						track.getMoods().add(Mood.getById(Integer.parseInt(mood.trim())));
					}
				}
				String timesOfDay = cur.getString(timesOfDayIndex);
				if (!timesOfDay.isEmpty()) {
					String[] t = timesOfDay.split(DELIMITER);
					for (String timeOfDay : t) {
						track.getTimesOfDay().add(TimeOfDay.valueOf(timeOfDay.trim()));
					}
				}
				String seasons = cur.getString(seasonsIndex);
				if (!seasons.isEmpty()) {
					String[] s = seasons.split(DELIMITER);
					for (String season : s) {
						track.getSeasons().add(Season.valueOf(season.trim()));
					}
				}
				String weathers = cur.getString(weathersIndex);
				if (!weathers.isEmpty()) {
					String[] w = weathers.split(DELIMITER);
					for (String weather : w) {
						track.getWeatherConditions().add(Condition.valueOf(weather.trim()));
					}
				}
			}
			cur.close();
		}
	}
	
	/**
	 * loads the album art (if available)
	 * 
	 * @param track
	 * @return album art if could be loaded successfully, null otherwise
	 */
	public Bitmap loadAlbumArt(Track track) {
		try {
			MediaMetadataRetriever retriever = new MediaMetadataRetriever();
			retriever.setDataSource(MusicHandler.deEscapePath(track.getData()));
			byte[] albumArt = retriever.getEmbeddedPicture();
			retriever.release();
		
			// try to load album art
			try {
				return scaleAlbumArt(BitmapFactory.decodeByteArray(albumArt, 0, albumArt.length));
			} catch (Exception e) {
				Log.e(TAG, "Album art not available.");
				// error loading it
				return null;
			}
		} catch (OutOfMemoryError oome) {
			// out of memory
			Log.e(TAG, "OutOfMemoryError while loading the album art");
			oome.printStackTrace();
			return null;
		}
	}
	
	/**
	 * scales a bitmap to a defined size
	 * 
	 * @param bm
	 * @return
	 */
	private Bitmap scaleAlbumArt(Bitmap bm) {
		return Bitmap.createScaledBitmap(bm, 200, 200, false);
	}
	
	/**
	 * replaces illegal characters
	 * 
	 * @param s
	 * @return String
	 */
	public static String escapePath(String s) {
		s = s.replaceAll("'", "\\%27");
		s = s.replaceAll(" ", "\\%20");
		return s;
	}
	
	/**
	 * undoes replaced illegal characters
	 * 
	 * @param s
	 * @return
	 */
	public static String deEscapePath(String s) {
		s = s.replaceAll("\\%27", "'");
		s = s.replaceAll("\\%20", " ");
		return s;
	}
	
	/**
	 * creates ContentValues with all attributes of DATABASE_TABLE_MUSIC
	 * 
	 * @param track
	 * @return ContentValues
	 */
	private ContentValues getContentValues(Track track) {
		ContentValues args = new ContentValues();
		args.put(KEY_DATA, track.getData());
		args.put(KEY_ID_DEVICE, track.getId());
		args.put(KEY_ID_DB, track.getIdDatabase());
		args.put(KEY_STATUS, track.getStatus().toString());
		args.put(KEY_ART, track.getArtLink());
		args.put(KEY_BPM, track.getBpm());
		
		String moods = "";
		for (Mood mood : track.getMoods()) {
			if (!moods.isEmpty())
				moods += ",";
			moods += mood.getId();
		}
		args.put(KEY_MOODS, moods);
		
		String timesOfDay = "";
		for (TimeOfDay timeOfDay : track.getTimesOfDay()) {
			if (!timesOfDay.isEmpty())
				timesOfDay += ",";
			timesOfDay += timeOfDay;
		}
		args.put(KEY_TIMESOFDAY, timesOfDay);
		
		String seasons = "";
		for (Season season : track.getSeasons()) {
			if (!seasons.isEmpty())
				seasons += ",";
			seasons += season.toString();
		}
		args.put(KEY_SEASONS, seasons);
		
		String weathers = "";
		for (Condition condition : track.getWeatherConditions()) {
			if (!weathers.isEmpty())
				weathers += ",";
			weathers += condition.toString();
		}
		args.put(KEY_WEATHERS, weathers);
		
		return args;
	}
	
	/**
	 * loads remaining data of a track from external database (last.fm)
	 * 
	 * @param artist
	 * @param title
	 * @return array
	 */
	public String[] loadRemainingData(String artist, String title) {
		// try to load remaining data from the database
		Log.i(TAG, "Loading data from the database...");
		
		String spec = "http://ws.audioscrobbler.com/2.0/?method=track.getinfo&api_key=b25b959554ed76058ac220b7b2e0a026"
				+ "&artist=" + artist + "&track=" + title + "&autocorrect=1";
		spec = spec.replaceAll(" ", "\\%20");
		Log.i(TAG, spec);
		
		Document doc = null;
		try {
			doc = XMLParser.getDocument(spec);
		} catch (Exception e) {
			Log.e(TAG, "Error retrieving results from lastfm. Cannot load remaining data.");
			return null;
		}
		
		String id = null;
		String albumTitle = null;
		String albumImage = null;
		
		Element lfm = (Element) doc.getElementsByTagName("lfm").item(0);
		
		if (lfm.getAttribute("status").equals("ok")) {
			// track found
			Element track = (Element) lfm.getElementsByTagName("track").item(0);

			// id
			id = track.getElementsByTagName("id").item(0).getChildNodes().item(0)
					.getNodeValue().trim();

			// album
			NodeList albumList = track.getElementsByTagName("album");
			
			if (albumList.getLength() > 0) {
				Element album = (Element) albumList.item(0);
				// title
				NodeList albumTitleList = album.getElementsByTagName("title");
				if (albumTitleList.getLength() > 0) {
					albumTitle = albumTitleList.item(0).getChildNodes().item(0)
							.getNodeValue().trim();
				}
				// image
				NodeList albumImageList = album.getElementsByTagName("image");
				if (albumImageList.getLength() > 0) {
					albumImage = albumImageList.item(0).getChildNodes().item(0)
							.getNodeValue().trim();
				}
			}
		}
		
		return new String[]{ id, albumTitle, albumImage };
	}
	
	/**
	 * @return the first track in the checklist
	 */
	public Track getNextTrackFromChecklist() {
		return getNextTrackFromList(DATABASE_TABLE_CHECK);
	}
	
	/**
	 * @return the first track in the uploadlist
	 */
	public Track getNextTrackFromUploadlist() {
		return getNextTrackFromList(DATABASE_TABLE_UPLOAD);
	}
	
	/**
	 * @return the first track in the list
	 */
	public Track getNextTrackFromList(String table) {
		synchronized(db) {
			Cursor cur = db.query(table, new String[]{ KEY_DATA }, null, null, null, null, null);
			if (cur == null || !cur.moveToFirst()) {
				return null;
			}
			String data = cur.getString(cur.getColumnIndex(KEY_DATA));
			cur.close();
		
			Track track = loadTrackWithHelpOfPath(data, true);
			return track;
		}
	}
	
	/**
	 * completes a track with data from external database (last.fm)
	 * and updates the track in the database
	 * 
	 * @param artist
	 * @param title
	 * @return Result
	 */
	public Result completeWithRemaingData(Track track) {
		Result result = Result.Process_Done;
		
		String[] data = loadRemainingData(track.getArtist(), track.getTitle());
				
		if (data == null ) {
			// error retrieving data: do nothing
			// idDB is still 0, so it can be checked again next time
			result = Result.Process_DoneWithErrors;
		} else {
			// no errors retrieving data
			if (data[0] == null) {
				// track not found: set idDB to -1
				// avoids multiple data download of unknown tracks
				track.setIdDatabase(-1);
				result = Result.Process_DoneWithErrors;
			} else {
				// track found: store retrieved data
				// idDB is now > 0
				// tracks with these ids will not be checked again
				track.setIdDatabase(Long.parseLong(data[0]));
						
				// set album
				if (track.getAlbum().isEmpty() && data[1] != null) {
					track.setAlbum(data[1]);
				}
						
				// set image link
				if (track.getArtLink().isEmpty() && data[2] != null) {
					track.setArtLink(data[2]);
				}
			}
					
			// store changes
			updateTrack(track);
		}
		
		return result;
	}
	
	/**
	 * uploads a track to the online database
	 * 
	 * @param track
	 * @return
	 */
	public Result uploadTrack(Track track, String username, String password) {
		// weather
		String weather = "";
		for (Weather.Condition c : track.getWeatherConditions()) {
			weather += c + ",";
		}
		if (!weather.isEmpty()) {
			weather = "&weather=" + deleteLastCharacter(weather);
		}
		
		// season
		String season = "";
		for (Season s : track.getSeasons()) {
			season += s + ",";
		}
		if (!season.isEmpty()) {
			season = "&season=" + deleteLastCharacter(season);
		}
		
		// mood
		String mood = "";
		for (Mood m : track.getMoods()) {
			mood += m.getId() + ",";
		}
		if (!mood.isEmpty()) {
			mood = "&mood=" + deleteLastCharacter(mood);
		}
		
		// time of day
		String timeOfDay = "";
		for (TimeOfDay t : track.getTimesOfDay()) {
			timeOfDay += t + ",";
		}
		if (!timeOfDay.isEmpty()) {
			timeOfDay = "&timeOfDay=" + deleteLastCharacter(timeOfDay);
		}
		
		// override rating
		String ov = "&ov=yes";
		
		// define link
		String spec = "http://aj-nk.de/mrs/v2/main.php?method=add&user=" + username
				+ "&pw=" + password
				+ "&id=" + track.getIdDatabase()
				+ weather + season + mood + timeOfDay + ov;
		Log.i(TAG, spec);
		
		// execute link and retrieve result
		Document doc;
		try {
			doc = XMLParser.getDocument(spec);
		} catch(Exception e) {
			Log.i(TAG, "Error while sending account details.");
			e.printStackTrace();
			return Result.ParsingException;
		}
		
		// check if request was successful or faulty
		NodeList results = doc.getElementsByTagName("SUCCESS");
		
		// error
		if (results.getLength() == 0) {
			results = doc.getElementsByTagName("error");
			if (results.getLength() > 0) {
				// not allowed to override rating
				return Result.Rating_Already_Exists;
			}
			return Result.Database_Connection;
		}
		
		return Result.Process_Done;
	}
	
	/**
	 * deletes the last character of a string
	 * 
	 * @param s
	 * @return String
	 */
	private String deleteLastCharacter(String s) {
		return s.substring(0, s.length()-1);
	}
	
	/**
	 * checks if there is music available on the device
	 * 
	 * @return true if music is available, false otherwise
	 */
	public final boolean hasMusic() {
		// TODO optimize
		return !loadTracks(null, null, false).isEmpty();
	}
	
	/**
	 * compares tracks in our database with the ones on the device
	 * 
	 * @param listener
	 */
	public void prepareTracksLocalDatabase(AnalyzeMusicListener listener) {
		Cursor cur;
		
		// no hold and wait because this is the only method which needs db
		// and contentResolver at the same time, so any time both of them
		// will be available
				
		// query all music tracks in our internal database
		synchronized(db) {
			cur = db.query(DATABASE_TABLE_MUSIC, null, null, null, null, null, null);
			if (cur != null && cur.moveToFirst()) {
				int idDeviceIndex = cur.getColumnIndex(KEY_ID_DEVICE);
				int pathIndex = cur.getColumnIndex(KEY_DATA);
				int i = 0;
				do {
					long idDevice = cur.getLong(idDeviceIndex);
					String path = cur.getString(pathIndex);
					// get corresponding track on the device
					String pathToCompare = "";
					synchronized(contentResolver) {
						Cursor cur2 = contentResolver.query(MEDIA_URI, new String[]{ KEY_ID_DEVICE, KEY_DATA },
								KEY_ID_DEVICE + "=" + idDevice, null, null);
						if (cur2 != null && cur2.moveToFirst()) {
							pathToCompare = cur2.getString(cur2.getColumnIndex(KEY_DATA));
						}
						cur2.close();
					}
					// check if paths are equal
					if (!path.equals(escapePath(pathToCompare))) {
						// track exists in database, but is not available on the device
						// so delete it from database, check list and all playlists
						Log.i(TAG, "File not existing: " + path);
						Log.i(TAG, "Deleting it from checklist...");
						deleteTrackFromChecklist(path);
						Log.i(TAG, "Deleting it from playlists...");
						// TODO problem: pHandler not available
						// pHandler.removeTrackFromAllPlaylists(idDatabase);
						Log.i(TAG, "Deleting it from database...");
						deleteTrackWithHelpOfPath(path);
					}
					listener.onTrackAnalyzed(++i);
				} while(cur.moveToNext());
			}
			cur.close();
		}
	}
	
	/**
	 * compares tracks on the device with the ones in our database
	 */
	public void prepareTracksDevice(AnalyzeMusicListener listener) {
		Cursor cur;
		
		// no hold and wait because this is the only method which needs db
		// and contentResolver at the same time, so any time both of them
		// will be available
		
		// query all music tracks on the device
		synchronized(contentResolver) {
			cur = contentResolver.query(MEDIA_URI, null, null, null, null);
			if (cur != null && cur.moveToFirst()) {
				int i = 0;
					
				// no music in our database, so insert every track to MUSIC and CHECK
				// will be the case if the application is started for the first time
				// or if there was no music on the device so far
				if (getMusicInDbCount() == 0) {
					truncateChecklist();
						
					do {
						Track track = createTrack(cur);
						insertTrack(track);
						insertTrackToChecklist(track.getData(), false);
						listener.onTrackAnalyzed(++i);
					} while(cur.moveToNext());
						
					return;
					
					// TODO optimize:
					// do not use insert function for every track
					// this would be last much longer than approach below
					// problem: showing a progress bar is not possible any longer
						
					/*
					// get all tracks
					ArrayList<Track> tracks = new ArrayList<Track>();
					do {
						tracks.add(createTrack(cur));
						listener.onTrackAnalyzed(++i);
					} while(cur.moveToNext());
						
					listener.onAllTracksAnalyzed();
						
					// insert tracks to database
					String sql = "BEGIN TRANSACTION;";
					for (Track track : tracks) {
						sql += "INSERT INTO " + DATABASE_CREATE_MUSIC + " VALUES ('"
								+ track.getData() + "', " + track.getId() + ", '"
								+ track.getArtist() + "', '" + track.getTitle() + "', '"
								+ track.getAlbum() + "', " + track.getRelease() + ", "
								+ track.getDuration() + ");";
					}
						
					// insert tracks to checklist
					for (Track track : tracks) {
						sql += "INSERT INTO " + DATABASE_CREATE_CHECK + " VALUES ('"
								+ track.getData() + "');";
					}
					sql += "COMMIT;";
						
					db.execSQL(sql);
					return;
					*/
				}
					
				// music found in our database
				do {
					Track track = createTrack(cur);
					int count = getCount(MusicHandler.DATABASE_TABLE_MUSIC, KEY_DATA + "='" + track.getData() + "'");
						
					// track from device is missing in our database
					// so insert it to database, checklist, list of new tracks
					if (count == 0) {
						insertTrack(track);
						insertTrackToChecklist(track.getData(), true);
					}
						
					listener.onTrackAnalyzed(++i);
				} while(cur.moveToNext());
			}
			cur.close();
		}
	}
	
	public void downloadAndAddMissingRatings() {
		List<Long> trackIds = loadTrackIds();
		if (trackIds.isEmpty()) return;
		
		String link = "http://aj-nk.de/mrs/getRatings.php?user=" + aHandler.getUsername()
				+ "&ids=";
		for (long id : trackIds) {
			link += id + ";";
		}
		link = link.substring(0, link.length()-1);
		
		try {
			// TODO like parsing in computePlaylist (PlaylistHandler)
			// TODO optimize: only update tracks with rating unequal to the stored rating
			
			Document doc = XMLParser.getDocument(link);
			
			Node getratings = doc.getElementsByTagName("ratings").item(0);
			Element e = (Element) getratings;
			
			Log.i(TAG, "Parsing ratings...");
			NodeList ratings = e.getElementsByTagName("rating");
			
			for (int i = 0; i<ratings.getLength(); i++) {
				Node n = ratings.item(i);
				Element cur = (Element) n;
				
				Node curattr = cur.getElementsByTagName("id").item(0);
				Long curid = Long.parseLong(curattr.getTextContent().trim());
				Track t = loadTrackWithHelpOfDatabaseId(curid, true);
				if (t == null) continue;
				
				Element h;
				
				// moods
				try {
					Node m = cur.getElementsByTagName("moods").item(0);
					h = (Element) m;
					NodeList moods = h.getElementsByTagName("mood");
					
					t.getMoods().clear();
					for (int j = 0; j < moods.getLength(); j++) {
						curattr = moods.item(j);
						Mood mood = Mood.getById(Integer.parseInt(curattr.getTextContent().trim()));
						t.getMoods().add(mood);
					}
				} catch(NullPointerException npe) {
				}
				
				// seasons
				try {
					Node m = cur.getElementsByTagName("seasons").item(0);
					h = (Element) m;
					NodeList seasons = h.getElementsByTagName("season");
					
					t.getSeasons().clear();
					for (int j = 0; j < seasons.getLength(); j++) {
						curattr = seasons.item(j);
						Season season = Season.valueOf(curattr.getTextContent().trim());
						t.getSeasons().add(season);
					}
				} catch(NullPointerException npe) {
				}
				
				// times of day
				try {
					Node m = cur.getElementsByTagName("timesOfDay").item(0);
					h = (Element) m;
					NodeList timesOfDay = h.getElementsByTagName("timeOfDay");
					
					t.getTimesOfDay().clear();
					for (int j = 0; j < timesOfDay.getLength(); j++) {
						curattr = timesOfDay.item(j);
						TimeOfDay timeOfDay = TimeOfDay.valueOf(curattr.getTextContent().trim());
						t.getTimesOfDay().add(timeOfDay);
					}
				} catch(NullPointerException npe) {
				}
				
				// weather conditions
				try {
					Node m = cur.getElementsByTagName("weathers").item(0);
					h = (Element) m;
					NodeList weathers = h.getElementsByTagName("weather");
					
					t.getWeatherConditions().clear();
					for (int j = 0; j < weathers.getLength(); j++) {
						curattr = weathers.item(j);
						Condition condition = Condition.valueOf(curattr.getTextContent().trim());
						t.getWeatherConditions().add(condition);
					}
				} catch(NullPointerException npe) {
				}
				
				// set status and store track
				t.setStatus(Status.RATED);
				updateTrack(t);
			}
		} catch(Exception e) {
			Log.i(TAG, "Error parsing the downloaded ratings.");
			e.printStackTrace();
		}
	}
	
	/**
	 * @return count of available tracks on the device
	 */
	public int getMusicOnDeviceCount() {
		int count = 0;
		synchronized(contentResolver) {
			Cursor cur = contentResolver.query(MEDIA_URI, new String[]{ "count(*)" }, null, null, null);
			if (cur != null && cur.moveToFirst()) {
				count = cur.getInt(0);
			}
			cur.close();
		}
		return count;
	}
	
	/**
	 * @return count of available tracks in our database
	 */
	public int getMusicInDbCount() {
		return getCount(DATABASE_TABLE_MUSIC, null);
	}
	
	/**
	 * @return count of tracks in the checklist
	 */
	public int getCheckCount() {
		return getCount(DATABASE_TABLE_CHECK, null);
	}
	
	/**
	 * @return count of tracks in the uploadlist
	 */
	public int getUploadCount() {
		return getCount(DATABASE_TABLE_UPLOAD, null);
	}
}
